VERSION 5.00
Begin VB.Form frmAddOperators 
   BorderStyle     =   3  'Fixed Dialog
   Caption         =   "Operator Management"
   ClientHeight    =   4935
   ClientLeft      =   45
   ClientTop       =   435
   ClientWidth     =   9840
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   4935
   ScaleWidth      =   9840
   ShowInTaskbar   =   0   'False
   Begin VB.ListBox lstUsers 
      Appearance      =   0  'Flat
      Height          =   3930
      Left            =   6360
      TabIndex        =   11
      Top             =   600
      Width           =   2775
   End
   Begin VB.Frame Frame1 
      Caption         =   "Operators:"
      Height          =   4095
      Left            =   600
      TabIndex        =   0
      Top             =   480
      Width           =   5175
      Begin VB.ComboBox cmbCity 
         Height          =   315
         Left            =   2040
         TabIndex        =   14
         Top             =   2760
         Width           =   1575
      End
      Begin VB.CommandButton Command2 
         Caption         =   "New"
         Height          =   375
         Left            =   480
         TabIndex        =   12
         Top             =   3360
         Width           =   975
      End
      Begin VB.ComboBox cmbAcc 
         Height          =   315
         Left            =   2040
         TabIndex        =   9
         Top             =   2280
         Width           =   1575
      End
      Begin VB.CommandButton Command1 
         Caption         =   "Submit"
         Height          =   375
         Left            =   3240
         TabIndex        =   8
         Top             =   3360
         Width           =   975
      End
      Begin VB.TextBox Text3 
         Appearance      =   0  'Flat
         Height          =   495
         Left            =   2040
         TabIndex        =   7
         Top             =   1560
         Width           =   2175
      End
      Begin VB.TextBox Text2 
         Appearance      =   0  'Flat
         Height          =   495
         IMEMode         =   3  'DISABLE
         Left            =   2040
         PasswordChar    =   "*"
         TabIndex        =   4
         Top             =   960
         Width           =   2175
      End
      Begin VB.TextBox Text1 
         Appearance      =   0  'Flat
         Height          =   495
         Left            =   2040
         TabIndex        =   2
         Top             =   360
         Width           =   2175
      End
      Begin VB.Label Label6 
         Caption         =   "City:"
         Height          =   375
         Left            =   480
         TabIndex        =   13
         Top             =   2760
         Width           =   1215
      End
      Begin VB.Label Label5 
         Caption         =   "Access:"
         Height          =   375
         Left            =   480
         TabIndex        =   10
         Top             =   2280
         Width           =   1215
      End
      Begin VB.Label Label4 
         Caption         =   "Nick Name:"
         Height          =   375
         Left            =   480
         TabIndex        =   6
         Top             =   1680
         Width           =   1215
      End
      Begin VB.Label Label2 
         Caption         =   "Password:"
         Height          =   375
         Left            =   480
         TabIndex        =   3
         Top             =   1080
         Width           =   1215
      End
      Begin VB.Label Label1 
         Caption         =   "Username:"
         Height          =   375
         Left            =   480
         TabIndex        =   1
         Top             =   480
         Width           =   1215
      End
   End
   Begin VB.Label Label3 
      Caption         =   "Username:"
      Height          =   375
      Left            =   1080
      TabIndex        =   5
      Top             =   2160
      Width           =   1215
   End
End
Attribute VB_Name = "frmAddOperators"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim cn As New ADODB.Connection

'if this operator is exist, update the record
'otherwise, insert a new record
Private Sub Command1_Click()
    Dim sql As String
    Dim rs As New ADODB.Recordset
    Dim username As String
    Dim bResult As Boolean
    
    bResult = False
    If (Text1.Text = "") Or (Text2.Text = "") Or (Text3.Text = "") Then
        MsgBox "Please complete the application form!", vbOKOnly, SOFT_TITLE
        Exit Sub
    End If
    username = Trim(Text1.Text)
    sql = "select * from users"
    OperateDatabase rs, cn, sql
    While Not rs.EOF
        If rs.Fields(1).value = username Then
            bResult = True
        End If
        rs.MoveNext
    Wend
    
    Dim city_id As Integer
    Dim acc_id As Integer
                
    city_id = cmbCity.ListIndex + 1
    sql = "SELECT acc FROM user_access WHERE ID=" & cmbAcc.ListIndex + 1
    Call CloseRecordSet(rs)
    OperateDatabase rs, cn, sql
    acc_id = rs.Fields(0).value
    
    If bResult Then
        If vbOK = MsgBox("Are you going to update the data?", vbOKCancel, SOFT_TITLE) Then
            'UPDATE `users` SET `ID`=[value-1],`username`=[value-2],`passwd`=[value-3],`last_login`=[value-4],`Display`=[value-5],`acc`=[value-6] WHERE 1
            sql = "UPDATE `users` SET `username`='" & Trim(Text1.Text) & "', `passwd`='" & Trim(Text2.Text) & "', `Display`='" & Text3.Text & "', `acc`=" & acc_id & ", `City`=" & city_id & " where username='" & username & "'"
            Call CloseRecordSet(rs)
    
            OperateDatabase rs, cn, sql
            MsgBox "Updated!", vbOKOnly, SOFT_TITLE
        End If
    Else
        'insert a new record
            If vbOK = MsgBox("Are you going to add a new operator?", vbOKCancel, SOFT_TITLE) Then
                'INSERT INTO `users`(`ID`, `username`, `passwd`, `last_login`, `Display`, `acc`) VALUES ([value-1],[value-2],[value-3],[value-4],[value-5],[value-6])
                sql = "INSERT INTO `users`(`username`, `passwd`, `Display`, `acc`, `City`) VALUES ('" & Trim(Text1.Text) & "', '" & Trim(Text2.Text) & "', '" & Trim(Text3.Text) & "', " & acc_id & ", " & city_id & ")"
                Call CloseRecordSet(rs)
                OperateDatabase rs, cn, sql
                MsgBox "Inserted!", vbOKOnly, SOFT_TITLE
            End If
    End If
    Call UpdateUserList(rs)
End Sub

Private Sub Command2_Click()
    Text1.Text = ""
    Text2.Text = ""
    Text3.Text = ""
    cmbAcc.ListIndex = 0
    Text1.SetFocus
End Sub

Private Sub Form_Load()
    Dim sql As String
    Dim rs As New ADODB.Recordset
    Call Cn2SQL(cn)
    
    'load the access data
    sql = "select * from user_access order by ID ASC"
    Call CloseRecordSet(rs)
    OperateDatabase rs, cn, sql
    
    While Not rs.EOF
        cmbAcc.AddItem rs.Fields(2).value
        rs.MoveNext
    Wend
 
    cmbAcc.ListIndex = 0
    
    sql = "select * from cs_city_list order by city_id ASC"
    Call CloseRecordSet(rs)
    OperateDatabase rs, cn, sql
    
    While Not rs.EOF
        cmbCity.AddItem rs.Fields(1).value
        rs.MoveNext
    Wend
 
    cmbCity.ListIndex = 0
    'On Error GoTo openDB
    'sql = "SET NAMES gbk"
    'Call CloseRecordSet(rs)
    'OperateDatabase rs, cn, sql
'openDB:
    
    If Operator_ACC <> 5 Then
        cmbAcc.Enabled = False
        Command2.Enabled = False
        lstUsers.Enabled = False
        Text1.Enabled = False
        
        sql = "select * from users where username='" & Trim(frmLogin.txtUserName.Text) & "'"
        Call CloseRecordSet(rs)
        OperateDatabase rs, cn, sql
        If Not rs.EOF Then
            Text1.Text = rs.Fields(1).value
            Text2.Text = rs.Fields(2).value
            Text3.Text = rs.Fields(4).value
            cmbAcc.ListIndex = rs.Fields(5).value
        End If
    End If
    
    Call UpdateUserList(rs)
End Sub

Private Sub Form_Unload(Cancel As Integer)
    cn.Close
    Unload frmLogin
End Sub

Private Sub lstUsers_DblClick()
    Dim CurrentCnt As String
    Dim rs As New ADODB.Recordset
    Dim sql As String
    
    
    CurrentCnt = lstUsers.List(lstUsers.ListIndex)
    
    sql = "select * from users  as A LEFT JOIN user_access B ON A.acc=B.acc"
    OperateDatabase rs, cn, sql
    While Not rs.EOF
        If CurrentCnt = rs.Fields(4).value Then
            Dim city_index As Integer
            Text1.Text = rs.Fields(1).value
            Text3.Text = rs.Fields(4).value
            Text2.Text = ""
            'List index number comes from table user_access
            If (rs.Fields(7).value) Then
                cmbAcc.ListIndex = val(rs.Fields(7).value) - 1
            Else
                
            End If
            city_index = rs.Fields(6).value
            If city_index = 0 Then
                cmbCity.ListIndex = cmbCity.ListCount - 1
            Else
                cmbCity.ListIndex = city_index - 1
            End If
            
            Text2.SetFocus
            Exit Sub
        End If
        rs.MoveNext
    Wend
End Sub

Private Sub CloseRecordSet(ByRef rs As ADODB.Recordset)
On Error GoTo NoErr
        rs.Close
NoErr:
End Sub

Public Function UpdateUserList(rs As ADODB.Recordset)
    Dim sql As String
    sql = "select * from users"
    Call CloseRecordSet(rs)
    OperateDatabase rs, cn, sql
    lstUsers.Clear
    While Not rs.EOF
        lstUsers.AddItem rs.Fields(4).value
        rs.MoveNext
    Wend
End Function



